"""
SQL Expression Language Tutorial
please see http://docs.sqlalchemy.org/en/latest/core/tutorial.html
"""
import sqlalchemy


def ex1():
    """version"""
    print(sqlalchemy.__version__)


def ex2():
    """engine"""
    from sqlalchemy import create_engine
    engine = create_engine('mysql://stock:stockjj@127.0.0.1/test?charset=utf8', encoding='utf-8', convert_unicode=True,
                           echo=True)
    print(engine)


def ex3():
    """create table"""
    from sqlalchemy import create_engine
    from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
    metadata = MetaData()
    users = Table('users', metadata,
                  Column('id', Integer, primary_key=True),
                  Column('name', String(50)),
                  Column('fullname', String(50)),
                  mysql_engine='InnoDB',
                  mysql_charset='utf8'
                  )

    addresses = Table('addresses', metadata,
                      Column('id', Integer, primary_key=True),
                      Column('user_id', None, ForeignKey('users.id')),
                      Column('email_address', String(200), nullable=False),
                      mysql_engine='InnoDB',
                      mysql_charset='utf8'
                      )

    # Column('id', Integer, Sequence('user_id_seq'), primary_key=True)
    engine = create_engine('mysql://stock:stockjj@127.0.0.1/test?charset=utf8', encoding='utf-8', convert_unicode=True,
                           echo=True)
    metadata.create_all(engine)

def ex4():
    """insert"""
    from sqlalchemy import create_engine
    from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
    metadata = MetaData()
    users = Table('users', metadata,
                  Column('id', Integer, primary_key=True),
                  Column('name', String(50)),
                  Column('fullname', String(50)),
                  mysql_engine='InnoDB',
                  mysql_charset='utf8'
                  )
    ins = users.insert().values(name='jack成', fullname='Jack Jones')
    print(str(ins))
    engine = create_engine('mysql://stock:stockjj@127.0.0.1/test?charset=utf8', echo=True)
    conn = engine.connect()
    result = conn.execute(ins)
    conn.close()
    print(result.inserted_primary_key)


def ex5():
    """insert many"""
    from sqlalchemy import create_engine
    from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
    metadata = MetaData()
    addresses = Table('addresses', metadata,
                      Column('id', Integer, primary_key=True),
                      Column('user_id', None, ForeignKey('users.id')),
                      Column('email_address', String(200), nullable=False),
                      mysql_engine='InnoDB',
                      mysql_charset='utf8'
                      )
    engine = create_engine('mysql://stock:stockjj@127.0.0.1/test?charset=utf8', echo=True)
    conn = engine.connect()
    conn.execute(addresses.insert(), [
        {'user_id': 1, 'email_address': 'jack@yahoo.com'},
        {'user_id': 1, 'email_address': 'jack@msn.com'},
        {'user_id': 2, 'email_address': 'www@www.org'},
        {'user_id': 2, 'email_address': 'wendy@aol.com'},
    ])
    conn.close()


def ex6():
    """select"""
    from sqlalchemy import create_engine
    from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
    from sqlalchemy.sql import select
    metadata = MetaData()
    users = Table('users', metadata,
                  Column('id', Integer, primary_key=True),
                  Column('name', String(50)),
                  Column('fullname', String(50)),
                  mysql_engine='InnoDB',
                  mysql_charset='utf8'
                  )
    s = select([users])
    engine = create_engine('mysql://stock:stockjj@127.0.0.1/test?charset=utf8', echo=True)
    conn = engine.connect()
    result = conn.execute(s)

    for row in result:
        print(row)

    row = result.fetchone()
    print(row)
    result.close()
    conn.close()

def ex7():
    from sqlalchemy import create_engine
    from sqlalchemy.sql import text
    s = text(
        "SELECT users.fullname || ', ' || addresses.email_address AS title "
        "FROM users, addresses "
        "WHERE users.id = addresses.user_id "
        "AND users.name BETWEEN :x AND :y "
        "AND (addresses.email_address LIKE :e1 "
        "OR addresses.email_address LIKE :e2)")

    engine = create_engine('mysql://stock:stockjj@127.0.0.1/test?charset=utf8', echo=True)
    conn = engine.connect()
    result = conn.execute(s, x='m', y='z', e1='%@aol.com', e2='%@msn.com').fetchall()
    for row in result:
        print(row)
    conn.close()


if __name__ == '__main__':
    ex7()
